package com.sun.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;



public class ExcelUtil {
	public List<Object> ExcelUtil(String  filePath) throws Exception {//工具类

		List<Object> list=new ArrayList<Object>();
		File file = new File(filePath);
		InputStream inputStream = null;
		Workbook workbook = null;
		if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
		{
			System.out.println("文件不是excel类型");
		}
		inputStream = new FileInputStream(file);
		workbook = WorkbookFactory.create(inputStream);
		inputStream.close();
		//工作表对象
		Sheet sheet = workbook.getSheetAt(0);
		//总行数
		int rowLength = sheet.getLastRowNum()+1;
		//工作表的列
		Row row = sheet.getRow(0);
		//总列数
		int colLength = row.getLastCellNum();
		//得到指定的单元格
		Cell cell = row.getCell(0);
		//得到单元格样式
		CellStyle cellStyle = cell.getCellStyle();
		System.out.println("行数：" + rowLength + ",列数：" + colLength);
		// 获取标题内容
		Row rowTitle = sheet.getRow(0);
		if (rowTitle != null) {
			int cellCount = rowTitle.getPhysicalNumberOfCells();// 获取列数
			for (int cellNum = 0; cellNum < cellCount; cellNum++) {
				 cell = rowTitle.getCell(cellNum);
				if (cell != null) {
					// int cellType=cell.getCellType();
					String cellValue = cell.getStringCellValue();
					System.out.print(cellValue + "|");
				}
			}
			System.out.println();
		}
		// 获取表中的数据
		int rowCount = sheet.getPhysicalNumberOfRows();// 获取行数
		for (int rowNum = 1; rowNum < rowCount; rowNum++) {
			Row rowData = sheet.getRow(rowNum);
			if (rowData != null) {
				// 读取列
				int cellCount = rowTitle.getPhysicalNumberOfCells();
				for (int cellNum = 0; cellNum < cellCount; cellNum++) {
					System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
					 cell = rowData.getCell(cellNum);
					// 匹配列的数据类型
				
					
					if (cell != null) {
						
						int cellType = cell.getCellType();
						String cellValue = "";
						switch (cellType) {
						case Cell.CELL_TYPE_STRING:// 字符串
							System.out.print("[string]");
							cellValue = cell.getStringCellValue();
							list.add(cellValue);
							break;
						case Cell.CELL_TYPE_BOOLEAN:// 布尔
							System.out.print("boolean");
							cellValue = String.valueOf(cell.getBooleanCellValue());
							list.add(cellValue);
							break;
						case Cell.CELL_TYPE_BLANK:// 空
							System.out.print("blank");
							list.add(cellValue);
							break;
						case Cell.CELL_TYPE_NUMERIC:// 数字(日期，普通数字)
							System.out.print("NUMERIC");
							if (HSSFDateUtil.isCellInternalDateFormatted(cell)) {// 日期
								System.out.print("日期");
								Date date = cell.getDateCellValue();
								cellValue = new DateTime(date).toString("yyyy-MM-dd");
								list.add(cellValue);

							} else {// 数字转换为字符串
									//
								System.out.print("数字转换为字符串");
								cell.setCellType(Cell.CELL_TYPE_STRING);
								cellValue = cell.toString();
								list.add(cellValue);
							}

							break;
						case Cell.CELL_TYPE_ERROR:// 布尔
							System.out.println("数据类型error");
							
							break;
						}
						System.out.println(cellValue);
						
					}
				}
			}
		}

		// 关闭流
		inputStream.close();
		return list;
	}

	/*public static void main(String[] args) {
		String filePath = "E:\\Mybatis\\Experiment2_Mybatis\\springMVC\\SpringMVC_POI\\src\\test\\java\\学生成绩表.xlsx";

		try {

			ExcelUtil excelUtil = new ExcelUtil();
			List<Object>list=excelUtil.ExcelUtil(filePath);
			List<Student> list1=new ArrayList<Student>();


			for (Object o : list) {
				System.out.println(o.toString());

			}
			for (int i=0;i<list.size();i++){
				Student pojo=new Student();
				for(int j=0;j<=7;j++){
					switch (j){
						case 0:pojo.setId(list.remove(0).toString());break;
						case 1:pojo.setName(list.remove(0).toString());break;
						case 2:pojo.setChineseScore(Double.parseDouble(list.remove(0).toString()));break;
						case 3:pojo.setMathScore(Double.parseDouble(list.remove(0).toString()));break;
						case 4:pojo.setEnglishScore(Double.parseDouble(list.remove(0).toString()));break;
						case 5:pojo.setPhysicalScore(Double.parseDouble(list.remove(0).toString()));break;
						case 6:pojo.setChemicalScore(Double.parseDouble(list.remove(0).toString()));break;
						case 7:pojo.setGeographyScore(Double.parseDouble(list.remove(0).toString()));break;


					}
				}
				list1.add(pojo);
			}
			for(Student student1:list1){
				System.out.println(student1.toString());
				System.out.println();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}*/
}
